package com.ruoyi.web.service;

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

@Service
public class C2DeployFailedDistService {

    @Autowired
    private DruidDataSource druidDataSource;

    public Map<String, Integer> getResponsiblePartyStatistics() {
        // SQL 查询语句：统计 responsibleParty 出现的次数，并排除 'success'
        String sql = "SELECT `responsibleParty`, COUNT(*) AS `count` " +
                "FROM `ota_info` " +
                "WHERE `responsibleParty` <> 'success' " +
                "GROUP BY `responsibleParty`;";
        System.out.println("Executing SQL: " + sql);

        // 存储统计结果
        Map<String, Integer> statistics = new HashMap<>();

        try (Connection conn = druidDataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {

            while (rs.next()) {
                String responsibleParty = rs.getString("responsibleParty");
                int count = rs.getInt("count");

                statistics.put(responsibleParty, count); // 保存统计数据
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return statistics;
    }

    public Map<String, Integer> getCurrentWeekStatistics() {
        // SQL 查询语句：统计当前周数据
        String sql = "SELECT `responsibleParty`, COUNT(*) AS `count` " +
                "FROM `ota_info` " +
                "WHERE `responsibleParty` <> 'success' " +
                "AND YEARWEEK(`otaTime`, 1) = YEARWEEK(CURDATE(), 1) " +
                "GROUP BY `responsibleParty`;";
        System.out.println("Executing SQL: " + sql);

        // 存储统计结果
        Map<String, Integer> statistics = new HashMap<>();

        try (Connection conn = druidDataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {

            while (rs.next()) {
                String responsibleParty = rs.getString("responsibleParty");
                int count = rs.getInt("count");
                statistics.put(responsibleParty, count);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return statistics;
    }

    public Map<String, Integer> getCurrentMonthStatistics() {
        // SQL 查询语句：统计当前月数据
        String sql = "SELECT `responsibleParty`, COUNT(*) AS `count` " +
                "FROM `ota_info` " +
                "WHERE `responsibleParty` <> 'success' " +
                "AND YEAR(`otaTime`) = YEAR(CURDATE()) " +
                "AND MONTH(`otaTime`) = MONTH(CURDATE()) " +
                "GROUP BY `responsibleParty`;";
        System.out.println("Executing SQL: " + sql);

        Map<String, Integer> statistics = new HashMap<>();

        try (Connection conn = druidDataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {

            while (rs.next()) {
                String responsibleParty = rs.getString("responsibleParty");
                int count = rs.getInt("count");
                statistics.put(responsibleParty, count);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return statistics;
    }

    public Map<String, Integer> getCurrentQuarterStatistics() {
        // SQL 查询语句：统计当前季度数据
        String sql = "SELECT `responsibleParty`, COUNT(*) AS `count` " +
                "FROM `ota_info` " +
                "WHERE `responsibleParty` <> 'success' " +
                "AND YEAR(`otaTime`) = YEAR(CURDATE()) " +
                "AND QUARTER(`otaTime`) = QUARTER(CURDATE()) " +
                "GROUP BY `responsibleParty`;";
        System.out.println("Executing SQL: " + sql);

        Map<String, Integer> statistics = new HashMap<>();

        try (Connection conn = druidDataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {

            while (rs.next()) {
                String responsibleParty = rs.getString("responsibleParty");
                int count = rs.getInt("count");
                statistics.put(responsibleParty, count);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return statistics;
    }

    public Map<String, Integer> getCurrentYearStatistics() {
        // SQL 查询语句：统计当前年度数据
        String sql = "SELECT `responsibleParty`, COUNT(*) AS `count` " +
                "FROM `ota_info` " +
                "WHERE `responsibleParty` <> 'success' " +
                "AND YEAR(`otaTime`) = YEAR(CURDATE()) " +
                "GROUP BY `responsibleParty`;";
        System.out.println("Executing SQL: " + sql);

        Map<String, Integer> statistics = new HashMap<>();

        try (Connection conn = druidDataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {

            while (rs.next()) {
                String responsibleParty = rs.getString("responsibleParty");
                int count = rs.getInt("count");
                statistics.put(responsibleParty, count);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return statistics;
    }
}